package com.ryf.maven.dao;

import com.ryf.maven.entity.Student;
import com.ryf.maven.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description
 * @ClassName StudentDao
 * @Author L-Cohen
 * @date 2020.11.30 23:18
 */
public class StudentDao {
    public static Student getStudent(ResultSet resultSet) throws SQLException {
        String stuNo = resultSet.getString("stuNo");
        String stuName = resultSet.getString("stuName");
        String sex = resultSet.getString("sex");
        String birth = resultSet.getString("birth");
        String nat = resultSet.getString("nat");
        String s_class = resultSet.getString("s_class");

        Student student = new Student();
        student.setStuNo(stuNo);
        student.setStuName(stuName);
        student.setSex(sex);
        student.setBirth(birth);
        student.setNat(nat);
        student.setS_class(s_class);

        return student;
    }

    public static List<Student> getStudent(int page, int limit){
        List<Student> students = new ArrayList<Student>();
        Connection connection = DBUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareCall("select * from student limit ?, ?");
            statement.setInt(1, (page - 1) * limit);
            statement.setInt(2, limit);
            resultSet = statement.executeQuery();
            while (resultSet.next())
            {
                Student student1 = getStudent(resultSet);
                students.add(student1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        DBUtil.close(resultSet, statement, connection);
        return students;
    }

    public static List<Student> getStudent()
    {
        return getStudent(1, 10);
    }


    public static Student getStuByStuNo(String stuNo){
        Connection connection = DBUtil.getConnection();
        String sql = "select * from student where stuNo = ?";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Student student = null;
        try {
            statement = connection.prepareCall(sql);
            // 设置sql参数
            statement.setString(1, stuNo);
            resultSet = statement.executeQuery();
            if (resultSet != null && resultSet.next())
            {
                student = getStudent(resultSet);
            }
            DBUtil.close(resultSet, statement, connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return student;
    }

    public static int delStuByStuNo(String stuNo){
        Connection connection = DBUtil.getConnection();
        String sql = "delete from student where stuNo = ?";
        int rows = 0;
        try {
            PreparedStatement statement = connection.prepareCall(sql);
            statement.setString(1, stuNo);
            rows = statement.executeUpdate();
            DBUtil.close(null, statement, connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rows;
    }

    public static int updateStu(Student student){
        Connection connection = DBUtil.getConnection();
        String sql = "update student set stuName = ?, sex = ?, birth = ?, nat = ?, s_class = ? where stuNo = ?";
        int rows = 0;
        try {
            PreparedStatement statement = connection.prepareCall(sql);
            statement.setString(1, student.getStuName());
            statement.setString(2, student.getSex());
            statement.setString(3, student.getBirth());
            statement.setString(4, student.getNat());
            statement.setString(5, student.getS_class());
            statement.setString(6, student.getStuNo());

            rows = statement.executeUpdate();
            DBUtil.close(null, statement, connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rows;
    }

    public static int addStu(Student student){
        Connection connection = DBUtil.getConnection();
        String sql = "insert into student (stuNo, stuName, sex, birth, nat, s_class) values(?,?,?,?,?,?)";
        int rows = 0;
        try {
            PreparedStatement statement = connection.prepareCall(sql);
            statement.setString(1, student.getStuNo());
            statement.setString(2, student.getStuName());
            statement.setString(3, student.getSex());
            statement.setString(4, student.getBirth());
            statement.setString(5, student.getNat());
            statement.setString(6, student.getS_class());

            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rows;
    }

    public static int getCount()
    {
        Connection connection = DBUtil.getConnection();
        String sql = "select count(*) from  student";
        int rows = 0;
        try {
            PreparedStatement statement = connection.prepareCall(sql);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next())
            {
                rows = resultSet.getInt(1);
            }
            DBUtil.close(resultSet, statement, connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rows;
    }


    public static void main(String[] args) {
        Student student = getStuByStuNo("20001");
        System.out.println(student);
    }
}
